package modelosqlserver;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.SwingUtilities;

import transacciones.ConeccionDAO;
import transacciones.TransactionDelegate;
import utilitario.FuncionesUsuario;

public class PedidoDAO extends ConeccionDAO {

	private Pedido varPed;
	private FuncionesUsuario funUsu;

	public PedidoDAO(Pedido parPed, TransactionDelegate parTransactionDelegate, FuncionesUsuario parFunUsu) {

		super(parTransactionDelegate);
		this.varPed = parPed;
		this.funUsu = parFunUsu;

	}// Fin de PedidoDAO(Pedido parPed, FuncionesUsuario funUsu)

	public int agregar(Pedido parPed) {
		String conSql;
		PreparedStatement pstm;
		ResultSet resSql;
		int codIns = 0;
		try {

			conSql = "insert into pedido values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
			pstm = obtenerConeccion().prepareStatement(conSql, Statement.RETURN_GENERATED_KEYS);

			// pstm.setInt(1, parPed.getPed_cod());
			pstm.setInt(1, parPed.getCli_cod());
			pstm.setString(2, parPed.getPed_nomdoc());
			pstm.setString(3, parPed.getPed_numdoc());
			pstm.setString(4, parPed.getPed_nom());
			pstm.setString(5, parPed.getPed_dir());
			pstm.setString(6, parPed.getPed_docide());
			pstm.setString(7, parPed.getPed_numide());
			pstm.setDate(8, parPed.getPed_fecreg());
			pstm.setDate(9, parPed.getPed_fecent());
			pstm.setInt(10, parPed.getPed_temp());
			pstm.setDouble(11, parPed.getPed_imptot());
			pstm.setString(12, parPed.getPed_est());
			pstm.setInt(13, parPed.getPed_ver());

			if (pstm.executeUpdate() == 1) {
				resSql = pstm.getGeneratedKeys();
				while (resSql.next()) {

					codIns = resSql.getInt(1);

				}
				resSql.close();

			}// Fin de if(pstm.executeUpdate() == 1)

			pstm.close();
			return codIns;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}// Fin de Metodo Agregar Docente

	public int actualizar(Pedido parPed) {

		int codUpd = 0;
		String conSql;
		PreparedStatement pstm;
		try {

			conSql = "update pedido set cli_cod=?,ped_nomdoc=?,ped_numdoc=?,ped_nom=?,ped_dir=?,ped_docide=?," +
					"ped_numide=?,ped_fecreg=?,ped_fecent=?,ped_temp=?,ped_imptot=?,ped_est=?,ped_ver=? where ped_cod=? and ped_ver=?";

			pstm = obtenerConeccion().prepareStatement(conSql, Statement.RETURN_GENERATED_KEYS);

			pstm.setInt(1, parPed.getCli_cod());
			pstm.setString(2, parPed.getPed_nomdoc());
			pstm.setString(3, parPed.getPed_numdoc());
			pstm.setString(4, parPed.getPed_nom());
			pstm.setString(5, parPed.getPed_dir());
			pstm.setString(6, parPed.getPed_docide());
			pstm.setString(7, parPed.getPed_numide());
			pstm.setDate(8, parPed.getPed_fecreg());
			pstm.setDate(9, parPed.getPed_fecent());
			pstm.setInt(10, parPed.getPed_temp());
			pstm.setDouble(11, parPed.getPed_imptot());
			pstm.setString(12, parPed.getPed_est());
			pstm.setInt(13, parPed.getPed_ver() + 1);
			pstm.setInt(14, parPed.getPed_cod());
			pstm.setInt(15, parPed.getPed_ver());

			if (pstm.executeUpdate() == 1) {

				codUpd = parPed.getPed_cod();

			}// Fin de if(pstm.executeUpdate() == 1)
			pstm.close();
			return codUpd;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}// Fin de Metodo Agregar Docente

	public List<Integer> obtenerPedidoDetPedidoProducto(int parPrdCod) {
		String conSql;
		ResultSet resSql;
		PreparedStatement pstm;
		try {

			conSql = "select ped_cod from viewPedidoCliente where prd_cod = " + parPrdCod;

			pstm = obtenerConeccion().prepareStatement(conSql);
			resSql = pstm.executeQuery();
			List<Integer> lisPedCod =  new ArrayList<Integer>();
			
			if (resSql.next()) {

				lisPedCod.add(resSql.getInt("ped_cod"));

			}// Fin de if(resSql.next()){

			pstm.close();
			resSql.close();

			return lisPedCod;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}// Fin de Metodo Agregar Docente
	
	
	public Pedido obtenerPedido(int parPedCod) {
		String conSql;
		ResultSet resSql;
		PreparedStatement pstm;
		try {

			conSql = "select * from pedido where ped_cod = " + parPedCod;

			pstm = obtenerConeccion().prepareStatement(conSql);
			resSql = pstm.executeQuery();

			varPed.limpiarInstancia();
			if (resSql.next()) {

				varPed.setPed_cod(resSql.getInt("ped_cod"));
				varPed.setCli_cod(resSql.getInt("cli_cod"));
				varPed.setPed_nomdoc(resSql.getString("ped_nomdoc"));
				varPed.setPed_numdoc(resSql.getString("ped_numdoc"));
				varPed.setPed_nom(resSql.getString("ped_nom"));
				varPed.setPed_dir(resSql.getString("ped_dir"));
				varPed.setPed_docide(resSql.getString("ped_docide"));
				varPed.setPed_numide(resSql.getString("ped_numide"));
				varPed.setPed_fecreg(resSql.getDate("ped_fecreg"));
				varPed.setPed_fecent(resSql.getDate("ped_fecent"));
				varPed.setPed_imptot(resSql.getDouble("ped_imptot"));
				varPed.setPed_temp(resSql.getInt("ped_temp"));
				varPed.setPed_est(resSql.getString("ped_est"));
				varPed.setPed_ver(resSql.getInt("ped_ver"));

			}// Fin de if(resSql.next()){

			pstm.close();
			resSql.close();

			return varPed;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}// Fin de Metodo Agregar Docente

	public Pedido obtenerPedido(String parPedNomDoc, String parPedNumDoc) {
		String conSql;
		ResultSet resSql;
		PreparedStatement pstm;
		try {

			conSql = "select * from pedido where ped_nomdoc = '" + parPedNomDoc + "' and ped_numdoc = '" + parPedNumDoc + "'";

			pstm = obtenerConeccion().prepareStatement(conSql);
			resSql = pstm.executeQuery();

			varPed.limpiarInstancia();
			if (resSql.next()) {

				varPed.setPed_cod(resSql.getInt("ped_cod"));
				varPed.setCli_cod(resSql.getInt("cli_cod"));
				varPed.setPed_nomdoc(resSql.getString("ped_nomdoc"));
				varPed.setPed_numdoc(resSql.getString("ped_numdoc"));
				varPed.setPed_nom(resSql.getString("ped_nom"));
				varPed.setPed_dir(resSql.getString("ped_dir"));
				varPed.setPed_docide(resSql.getString("ped_docide"));
				varPed.setPed_numide(resSql.getString("ped_numide"));
				varPed.setPed_fecreg(resSql.getDate("ped_fecreg"));
				varPed.setPed_fecent(resSql.getDate("ped_fecent"));
				varPed.setPed_imptot(resSql.getDouble("ped_imptot"));
				varPed.setPed_est(resSql.getString("ped_est"));
				varPed.setPed_ver(resSql.getInt("ped_ver"));

			}// Fin de if(resSql.next()){

			pstm.close();
			resSql.close();

			return varPed;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}// Fin de Metodo Agregar Docente

	public List<String> obtenerListaPedido(String parSql) {

		ResultSet resSql;
		try {

			resSql = obtenerConeccion().createStatement().executeQuery(parSql);
			List<String> lisView = new ArrayList<String>();

			while (resSql.next()) {

				lisView.add(resSql.getString("ped_nomdoc") + "#"
						+ resSql.getString("ped_numdoc") + "#"
						+ resSql.getString("ped_nom") + "#"
						+ resSql.getString("prd_ide") + "#"
						+ resSql.getDate("ped_fecreg").getDate() + "/"
						+ (resSql.getDate("ped_fecreg").getMonth() + 1) + "/"
						+ (resSql.getDate("ped_fecreg").getYear() + 1900) + "#"
						+ resSql.getDate("ped_fecent").getDate() + "/"
						+ (resSql.getDate("ped_fecent").getMonth() + 1) + "/"
						+ (resSql.getDate("ped_fecent").getYear() + 1900) + "#"
						+ resSql.getString("ped_temp") + "#"
						+ resSql.getString("ped_est") + "#"
						+ resSql.getString("ped_cod") + "#"
						+ resSql.getString("cli_cod"));
			}

			resSql.close();
			return lisView;

		} catch (SQLException e) {
			// funUsu.mostrarMensaje("¡¡¡Error en metodo getListaContacto()", this.getClass().getName());
			throw new RuntimeException(e);

		}// Fin Catch en caso de que haya un error
	}// Fin de metodo obtenerListaDetPedido(int parPedCod)	
	public boolean eliminar(int parPedCod) {
		String conSql;
		PreparedStatement pstm;
		try {

			conSql = "delete from pedido where ped_cod=" + parPedCod;
			pstm = obtenerConeccion().prepareStatement(conSql);

			int afectadas = pstm.executeUpdate();
			pstm.close();

			return (afectadas == 1) ? true : false;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}// Fin de Metodo Agregar Docente

	public String obtenerDocumentoPedido(String parNomDoc, String parNumDoc) {

		String conSql;
		ResultSet resSql;
		try {

			if (parNomDoc.equals("PEDIDO")) {

				conSql = "select * from viewRepPedido where ped_cod='" + parNumDoc + "'";

			}// Fin de if (parTipDoc.equals("GUIA DE REMISION"))
			else {

				conSql = "select * from viewRepPedido where ped_nomdoc='" + parNomDoc + "' and ped_numdoc='" + parNumDoc + "'";

			}

			resSql = obtenerConeccion().createStatement().executeQuery(conSql);
			String resBus = "";

			if (resSql.next()) {
				resBus = resSql.getInt("ped_cod") + "#"
						+ resSql.getInt("emp_cod") + "#"
						+ resSql.getInt("suc_cod") + "#"
						+ resSql.getInt("per_cod") + "#"
						+ resSql.getInt("ctr_cod") + "#"
						+ resSql.getInt("vou_cod") + "#"
						+ resSql.getInt("eml_cod");
			}

			resSql.close();
			return resBus;

		} catch (SQLException e) {
			// funUsu.mostrarMensaje("¡¡¡Error en metodo getListaContacto()", this.getClass().getName());
			throw new RuntimeException(e);

		}// Fin Catch en caso de que haya un error
	}// fin de obtenerDocumentoPedido(String parNomDoc, String parNumDoc)

	public boolean buscarDocumentoPedido(String parPedNomDoc, String parPedNumDoc) {
		boolean res = false;
		ResultSet resSql;
		try {

			String conSql = "select * from viewRepPedido where ped_nomdoc='" + parPedNomDoc + "' and ped_numdoc='" + parPedNumDoc + "'";

			resSql = obtenerConeccion().createStatement().executeQuery(conSql);

			if (resSql.next()) {
				res = true;
			}
			resSql.close();
			return res;

		} catch (SQLException e) {
			// funUsu.mostrarMensaje("¡¡¡Error en metodo getListaContacto()", this.getClass().getName());
			throw new RuntimeException(e);

		}// Fin Catch en caso de que haya un error

	}// Fin de buscarDocumento(String parDocNom, String parVenNumDoc)

	public static void main(String[] args) {
		SwingUtilities.invokeLater(new Runnable() {
			@Override
			public void run() {
			}
		});
	}
}